hive sql综合训练（一）
1.1 环境准备
1.1.1 建表语句
创建并使用数据库
create database  class;
use  class;
-- 创建学生表
DROP TABLE IF EXISTS student;
create table if not exists student_info(
    stu_id string COMMENT '学生id',
    stu_name string COMMENT '学生姓名',
    birthday string COMMENT '出生日期',
    sex string COMMENT '性别'
)
row format delimited fields terminated by ','
stored as textfile;

图1-1 创建学生表
-- 创建课程表
DROP TABLE IF EXISTS course;
create table if not exists course_info(
    course_id string COMMENT '课程id',
    course_name string COMMENT '课程名',
    tea_id string COMMENT '任课老师id'
)
row format delimited fields terminated by ','
stored as textfile;

图1-2 创建课程表
-- 创建老师表
DROP TABLE IF EXISTS teacher;
create table if not exists teacher_info(
    tea_id string COMMENT '老师id',
    tea_name string COMMENT '学生姓名'
)
row format delimited fields terminated by ','
stored as textfile;

图1-3 创建老师表
-- 创建分数表
DROP TABLE IF EXISTS score;
create table if not exists score_info(
    stu_id string COMMENT '学生id',
    course_id string COMMENT '课程id',
    score int COMMENT '成绩'
)
row format delimited fields terminated by ','
stored as textfile;

图1-4 创建分数表
1.1.2 数据准备
（1）将如下4个文件放到/home/hadoop/shell/data目录下
   图1-5 数据准备
（2）数据样式说明
[hadoop@hadoop1 data]]$ vi student_info.txt
001,彭于晏,1995-05-16,男
002,胡歌,1994-03-20,男
003,周杰伦,1995-04-30,男
004,刘德华,1998-08-28,男
005,唐国强,1993-09-10,男
006,陈道明,1992-11-12,男
007,陈坤,1999-04-09,男
008,吴京,1994-02-06,男
009,郭德纲,1992-12-05,男
010,于谦,1998-08-23,男
011,潘长江,1995-05-27,男
012,杨紫,1996-12-21,女
013,蒋欣,1997-11-08,女
014,赵丽颖,1990-01-09,女
015,刘亦菲,1993-01-14,女
016,周冬雨,1990-06-18,女
017,范冰冰,1992-07-04,女
018,李冰冰,1993-09-24,女
019,邓紫棋,1994-08-31,女
020,宋丹丹,1991-03-01,女

[hadoop@hadoop1 data]]$ vi course_info.txt
01,语文,1003
02,数学,1001
03,英语,1004
04,体育,1002
05,音乐,1002
[hadoop@hadoop1 data]]$ vi teacher_info.txt
1001,张高数
1002,李体音
1003,王子文
1004,刘丽英
[hadoop@hadoop1 data]$ vim score_info.txt
001,01,94
002,01,74
004,01,85
005,01,64
006,01,71
007,01,48
008,01,56
009,01,75
010,01,84
011,01,61
012,01,44
013,01,47
014,01,81
015,01,90
016,01,71
017,01,58
018,01,38
019,01,46
020,01,89
001,02,63
002,02,84
004,02,93
005,02,44
006,02,90
007,02,55
008,02,34
009,02,78
010,02,68
011,02,49
012,02,74
013,02,35
014,02,39
015,02,48
016,02,89
017,02,34
018,02,58
019,02,39
020,02,59
001,03,79
002,03,87
004,03,89
005,03,99
006,03,59
007,03,70
008,03,39
009,03,60
010,03,47
011,03,70
012,03,62
013,03,93
014,03,32
015,03,84
016,03,71
017,03,55
018,03,49
019,03,93
020,03,81
001,04,54
002,04,100
004,04,59
005,04,85
007,04,63
009,04,79
010,04,34
013,04,69
014,04,40
016,04,94
017,04,34
020,04,50
005,05,85
007,05,63
009,05,79
015,05,59
018,05,87
1.1.3 插入数据
（1）插入数据
load data local inpath '/home/hadoop/shell/data/student_info.txt' into table student_info;
load data local inpath '/home/hadoop/shell/data/course_info.txt' into table course_info;
load data local inpath '/home/hadoop/shell/data/teacher_info.txt' into table teacher_info;
load data local inpath '/home/hadoop/shell/data/score_info.txt' into table score_info;

图1-6 插入数据
（2）验证插入数据情况
select * from student_info limit 5;
select * from course_info limit 5;
select * from teacher_info limit 5;
select * from score_info limit 5;
1.2 简单查询
1.1 查找特定条件
（1）查询姓名中带“冰”的学生名单
hive>
select
    *
from student_info
where stu_name like "%冰%";

图1-7 查询姓名中带“冰”的学生名单
（2） 查询姓“王”老师的个数
hive>
select
    count(*)  wang_count
from teacher_info
where tea_name like '王%';

图1-8 查询姓“王”老师的个数
（3） 检索课程编号为“04”且分数小于60的学生的课程信息，结果按分数降序排列
select
    stu_id,
    course_id,
    score
from score_info
where course_id ='04' and score<60
order by score desc;

图1-9 检索课程编号为“04”且分数小于60的学生的课程信息
（4）查询数学成绩不及格的学生和其对应的成绩，按照学号升序排序
 select
    distinct
    s.stu_id,
    s.stu_name,
    t1.score
from student_info s
join (
    select   distinct    *
    from score_info
    where course_id in (select distinct course_id from course_info where course_name='数学') and score < 60
    ) t1 on s.stu_id = t1.stu_id
order by s.stu_id;

图1-10查询数学成绩不及格的学生和其对应的成绩

1.3 汇总分析
1.3.1 汇总分析
（1） 如图1-11所示，查询编号为“02”的课程的总成绩
Select
      course_id,
    sum(score) score_sum
from score_info
where course_id='02'
group by course_id;

图1-11查询编号为“02”的课程的总成绩
（2）如图1-12所示，查询参加考试的学生个数
思路：对成绩表中的学号做去重并count
select
    count(distinct stu_id) stu_num
from score_info;

图1-12查询参加考试的学生个数

1.3.2 分组
（1）如图1-13所示， 查询各科成绩最高和最低的分，以如下的形式显示：课程号，最高分，最低分
思路：按照学科分组并使用max和min。
select
    course_id,
    max(score) max_score,
    min(score) min_score
from score_info
group by course_id;

图1-13查询各科成绩最高和最低的分
（2） 如图1-14所示，查询每门课程有多少学生参加了考试（有考试成绩）
select
    course_id,
    count(stu_id) stu_num
from score_info
group by course_id;

图1-14查询每门课程有多少学生参加了考试
（3）如图1-15所示，查询男生、女生人数
select
    sex,
    count(stu_id) count
from student_info
group by sex;

图1-15查询男生、女生人数
1.3.3 分组结果的条件
（1） 如图1-16所示，查询平均成绩大于60分的学生的学号和平均成绩
1）思路分析
（1）平均成绩：展开来说就是计算每个学生的平均成绩
（2）这里涉及到“每个”就是要分组了
（3）平均成绩大于60分，就是对分组结果指定条件
（4）首先要分组求出每个学生的平均成绩，筛选高于60分的，并反查出这批学生，统计出这些学生总的平均成绩。
2）hql实操
select
    stu_id,
    avg(score) score_avg
from score_info
group by stu_id
having score_avg > 60;

图1-16查询平均成绩大于60分的学生的学号和平均成绩
（2） 如图1-17所示，查询至少选修四门课程的学生学号
1）思路分析
（1）需要先计算出每个学生选修的课程数据，需要按学号分组
（2）至少选修两门课程：也就是每个学生选修课程数目>=4，对分组结果指定条件
2）Hql实操
hive>
select
    stu_id,
    count(course_id) course_count
from score_info
group by stu_id
having course_count >=4;

图1-17查询至少选修四门课程的学生学号


（3）如图1-18所示，查询同姓（假设每个学生姓名的第一个字为姓）的学生名单并统计同姓人数大于2的姓
思路：先提取出每个学生的姓并分组，如果分组的count>=2则为同姓
hive>
select
    t1.first_name,
    count(*) count_first_name
from ( select   stu_id, stu_name, substr(stu_name,0,1) first_name
         from student_info) t1
group by t1.first_name
having count_first_name >= 2;

图1-18查询同姓的学生名单并统计同姓人数大于2的姓
（4）如图1-19所示，查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
思路：按照课程号分组并求组内的平均值。
select
    course_id,
    avg(score) score_avg
from score_info
group by course_id
order by score_avg asc, course_id desc;
结果

图1-19 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
（5） 如图1-20所示，统计参加考试人数大于等于15的学科
按课程分组并统计组内人数，过滤条件大于等于15
hive>
select
    course_id,
    count(stu_id) stu_count
from score_info
group by course_id
having stu_count >= 15;
结果

图1-20 统计参加考试人数大于等于15的学科
1.3.4 查询结果排序&分组指定条件
(1)如图1-21所示，查询学生的总成绩并按照总成绩降序排序
思路：分组、sum、排序
hive>
select
    stu_id,
    sum(score) sum_score
from score_info
group by stu_id
order by sum_score desc;
结果

图1-21 查询学生的总成绩并按照总成绩降序排序
（2）如图1-22所示， 按照如下格式显示学生的语文、数学、英语三科成绩，没有成绩的输出为0，按照学生的有效平均成绩降序显示
学生id 语文 数学 英语 有效课程数 有效平均成绩
select
  si.stu_id,
  sum(if(ci.course_name='语文',score,0))  `语文`,
  sum(if(ci.course_name='数学',score,0))  `数学`,
  sum(if(ci.course_name='英语',score,0))  `英语`,
  count(*)  `有效课程数`,
  avg(si.score)  `平均成绩`
from
  score_info si
join
  course_info ci
on
  si.course_id=ci.course_id
group by
  si.stu_id
order by
  `平均成绩` desc
结果

图1-22 按照如下格式显示学生的语文、数学、英语三科成绩，没有成绩的输出为0，按照学生的有效平均成绩降序显示
（3）如图1-23所示，查询一共参加三门课程且其中一门为语文课程的学生的id和姓名
select
    t1.stu_id,
    s.stu_name
from (
         select t1.stu_id
         from (
                  select stu_id,
                         course_id
                  from score_info
                  where stu_id in (
                      select stu_id
                      from score_info
                      where course_id = "01"
                  )
              ) t1
         group by t1.stu_id
         having count(t1.course_id) = 3
     ) t2
join student_info s on t1.stu_id = s.stu_id;
结果

图1-23查询一共参加三门课程且其中一门为语文课程的学生的id和姓名

1.4 复杂查询
1.4.1 如图1-24所示，查询所有课程成绩均小于60分的学生的学号、姓名
select s.stu_id,
       s.stu_name
from (
         select stu_id,
                sum(if(score >= 60, 1, 0)) flag
         from score_info
         group by stu_id
         having flag = 0
     ) t1
         join student_info s on s.stu_id = t1.stu_id;
结果

图1-24 查询所有课程成绩均小于60分的学生的学号、姓名
1.4.2 如图1-25所示，查询出只选修了三门课程的全部学生的学号和姓名
解释：学生选修的课程数 = 3
select
    s.stu_id,
    s.stu_name
from student_info s
join (
    select
        stu_id,
        count(course_id) course_count
    from score_info
    group by stu_id
    having course_count =3
    ) t1
on s.stu_id = t1.stu_id;
结果

图1-25 查询出只选修了三门课程的全部学生的学号和姓名





II